package cn.com.codes.impExpManager.blh;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xmind.core.Core;
import org.xmind.core.CoreException;
import org.xmind.core.INotes;
import org.xmind.core.IPlainNotesContent;
import org.xmind.core.ISheet;
import org.xmind.core.ISummary;
import org.xmind.core.ITopic;
import org.xmind.core.IWorkbook;
import org.xmind.core.IWorkbookBuilder;

import com.opensymphony.webwork.ServletActionContext;

import cn.com.codes.caseManager.dto.CaseManagerDto;
import cn.com.codes.caseManager.dto.ImpCaseFromExpVo;
import cn.com.codes.caseManager.service.CaseManagerService;
import cn.com.codes.common.SecurityContextHolderHelp;
import cn.com.codes.common.util.StringUtils;
import cn.com.codes.framework.app.blh.BusinessBlh;
import cn.com.codes.framework.app.services.BaseService;
import cn.com.codes.framework.app.view.UniversalView;
import cn.com.codes.framework.app.view.View;
import cn.com.codes.framework.transmission.events.BusiRequestEvent;
import cn.com.codes.impExpManager.dto.ImpCaseInfo;
import cn.com.codes.impExpManager.dto.ImpExpCaseInfo;
import cn.com.codes.impExpManager.service.ImpExpManagerService;
import cn.com.codes.object.CaseExeHistory;
import cn.com.codes.object.CaseLibCategory;
import cn.com.codes.object.CaseLibTestCaseInfo;
import cn.com.codes.object.CasePri;
import cn.com.codes.object.CasePubTag;
import cn.com.codes.object.CaseType;
import cn.com.codes.object.OutlineInfo;
import cn.com.codes.object.SingleTestTask;
import cn.com.codes.object.SoftwareVersion;
import cn.com.codes.object.TestCaseInfo;
import cn.com.codes.object.TestCasePackage;
import cn.com.codes.object.TestCase_CasePkg;
import cn.com.codes.object.TypeDefine;
import cn.com.codes.object.UserTag;
import cn.com.codes.outlineManager.blh.OutLineManagerBlh;
import cn.com.codes.outlineManager.service.OutLineManagerService;
import cn.com.codes.testBaseSet.service.TestBaseSetService;
import cn.com.codes.testCaseLibManager.service.TestCaseLibManagerService;
import cn.com.codes.testCasePackageManage.dto.TestCasePackageDto;
import cn.com.codes.testCasePackageManage.service.TestCasePackageService;
import cn.com.codes.testTaskManager.service.TestTaskDetailService;

public class CaseImpExpBlh extends BusinessBlh {
	
	private static Logger logger = Logger.getLogger(CaseImpExpBlh.class);
	private TestTaskDetailService testTaskService;
	private ImpExpManagerService impExpManagerService;
	private TestBaseSetService testBaseSetService;
	private OutLineManagerService outLineService;
	private OutLineManagerBlh outLineBlh;
	private CaseManagerService caseService;
	private TestCasePackageService testCasePackageService;
	private  BaseService  myPmbaseService;
	private TestCaseLibManagerService  testCaseLibManagerService;
	
	

	
	public View convert2Excel(BusiRequestEvent req) {
		CaseManagerDto dto = super.getDto(CaseManagerDto.class, req);
		if (!dto.getImportFileFileName().endsWith(".xmind")) {
			super.writeResult("所传文件不是xmind文件");
			return super.globalAjax();	
		}
		List<ImpExpCaseInfo> caseList = buildXmindCaseInfo(dto);
		return buildDownLoadCaseTempl(dto,caseList);
	}
	public View impFromXmind(BusiRequestEvent req) {
		CaseManagerDto dto = super.getDto(CaseManagerDto.class, req);
		if (dto.getImportFileFileName().endsWith(".xls")||dto.getImportFileFileName().endsWith(".xlsx")) {
			
			return  this.impCase(req);
		}
		
		if (!dto.getImportFileFileName().endsWith(".xmind")) {
			super.writeResult("所传文件不是xmind文件");
			return super.globalAjax();	
		}
		//解析xmind 流文件 为 测试用例list 
		List<ImpExpCaseInfo> caseList = buildXmindCaseInfo(dto);
		dto.getImportFile().delete();
		String taskId = dto.getTaskId();
		String pkgId = dto.getPckId();
		//不是转excel,写用例到itest中后返回 
		return xmd2Itest(caseList,taskId,pkgId);
	}
	
	/**
	 *  把从Xmind 中解析，数据 case List,导到itest中
	 * @param dto
	 * @return
	 */
	private View xmd2Itest(List<ImpExpCaseInfo> caseList,String taskId,String pkgId) {
		List<TestCaseInfo> testCaseInfoList = new ArrayList<>(caseList.size());
		for(ImpExpCaseInfo currCaseInfo :caseList ) {
			TestCaseInfo testCaseInfo = new TestCaseInfo();
			testCaseInfo.setModuleName(currCaseInfo.getModuleName());
			testCaseInfo.setTestCaseDes(currCaseInfo.getTestCaseDes());
			testCaseInfo.setOperDataRichText(currCaseInfo.getTestCaseOperData());
			testCaseInfo.setExpResult(currCaseInfo.getExpResult());
			testCaseInfo.setTypeName(currCaseInfo.getTypeName());
			testCaseInfo.setPriName(currCaseInfo.getPriName());
			testCaseInfo.setRemark("xmind导入");
			testCaseInfo.setTestStatus(1);
			testCaseInfoList.add(testCaseInfo);
			
		}
		int[] result = this.impAddCase(testCaseInfoList, taskId, pkgId,false);
		super.writeResult("成功导入"+result[0]+ "条用例");
		if(pkgId!=null&&!"".equals(pkgId)) {
			//计算执行率
			testCasePackageService.reWriteEexRatio(pkgId);
		}
		return super.globalAjax();	
		
	}
	
	/**
	 *  从Xmind 中解析，数据为 case List
	 * @param dto
	 * @return
	 */
	private List<ImpExpCaseInfo> buildXmindCaseInfo(CaseManagerDto dto) {
		 List<ImpExpCaseInfo> calseList  = new ArrayList<ImpExpCaseInfo>();
		  IWorkbookBuilder workbookBuilder = Core.getWorkbookBuilder();
		  IWorkbook workbook = null;
		  try {                        
			workbook = workbookBuilder.loadFromFile(dto.getImportFile());
		} catch (IOException e) {
			logger.error(e.getMessage(), e);
			return calseList;
		} catch (CoreException e) {
			logger.error(e.getMessage(), e);
			return calseList;
		}
		  ISheet sheet = workbook.getPrimarySheet();
	      //获取根节点
	      ITopic rootTopic = sheet.getRootTopic();
	      //获取根主题的文字内容
	      String rootTopicText = rootTopic.getTitleText();
	      //获取根主题的子主题
	      List<ITopic> itopics = rootTopic.getAllChildren();
	      //遍历根主题的子主题
	     
	      for(ITopic childtopic:itopics){
	    	  Integer level = 1;
	    	  topicRecursion(calseList,childtopic,rootTopicText,level);
	       }
		return calseList;
	}
	
    /**
     * @Description: 遍历子主题，获取每条案例的用例目录、用例名称、前置条件、执行步骤、预期结果
     * @param rootTopic:根主题
     * @param casePath:用例目录
     */
    public  void topicRecursion(List<ImpExpCaseInfo> calseList , ITopic rootTopic, String casePath, Integer level) {
        List<ITopic> childTopics = rootTopic.getChildren(ITopic.ATTACHED);
        //如果子主题大于2，证明是场景分类，需要再次进入递归
        if (childTopics.size()>1){
            //更新用例目录
        	String updatePath = "";
        	if(level>=8) { //导入的时候只支持8级，按斜杠分工的，超过6级就不用斜杠隔开，一横杠加到6级后
        		updatePath = casePath +"--"+rootTopic.getTitleText();
        	}else {
        		updatePath = casePath +"/"+rootTopic.getTitleText();
        	}
        	//updatePath = casePath +"/"+rootTopic.getTitleText();
            level ++;
            for (ITopic childtopic: childTopics){
                topicRecursion(calseList,childtopic, updatePath,level);
            }
        }
        else {
            //如果子主题个数不大于2，有两种可能，第一种是该业务场景分类只有1类，所以用try去尝试
        	
            try {
                rootTopic.getChildren(ITopic.ATTACHED).get(0).getChildren(ITopic.ATTACHED).get(0).getChildren(ITopic.ATTACHED).get(0);
            	String updatePath = "";
            	if(level>=6) {
            		updatePath = casePath +"--"+rootTopic.getTitleText();
            	}else {
            		updatePath = casePath +"/"+rootTopic.getTitleText();
            	}
            	//updatePath = casePath +"/"+rootTopic.getTitleText();
                level ++;
                topicRecursion(calseList,rootTopic.getChildren(ITopic.ATTACHED).get(0),updatePath,level);
            }
            //子主题个数不大于2，还有一种可能是该子主题已经是案例名称级别
            catch (IndexOutOfBoundsException ex) {
                String caseName = rootTopic.getTitleText();                         //案例名称
                String caseStep = "";
                String caseCondition = "";
                String caseResult = "";
                //获取案例步骤
                try {
                    INotes notes = rootTopic.getNotes();
                    IPlainNotesContent newplainContent=(IPlainNotesContent)notes.getContent(INotes.PLAIN);
                    Set<ISummary> summarySet =  rootTopic.getSummaries();
                  
                    Set<String > lableSet =   rootTopic.getLabels();
                    if(summarySet!=null&&!summarySet.isEmpty()) {
                    	System.out.println(summarySet.iterator().next());
                    }
                    if(lableSet!=null&&!lableSet.isEmpty()) {
                    	System.out.println(lableSet.iterator().next());
                    }
                    if(newplainContent!=null) {
                    	caseStep = newplainContent.getTextContent();
                    }
                    
                }catch (NullPointerException e){
                	logger.error(e.getMessage(), e);
                }
                try {
                    List<ITopic> caseConTopics = rootTopic.getChildren(ITopic.ATTACHED);
                    if(caseConTopics!=null&&!caseConTopics.isEmpty()) {
                        caseCondition = caseConTopics.get(0).getTitleText();         //前置条件
                        //caseConTopics.get(0).gets
                        List<ITopic> caseResultTopics = caseConTopics.get(0).getAllChildren();
                        if(caseResultTopics!=null&&!caseResultTopics.isEmpty()) {
                        	caseResult = caseResultTopics.get(0).getTitleText();         //预期结果
                        }
                        
                    }
                }catch (IndexOutOfBoundsException exOUB) {
                	logger.error(exOUB.getMessage(), exOUB);
                }

                ImpExpCaseInfo caseInfo = new ImpExpCaseInfo();
                caseInfo.setStatus(1);
            	caseInfo.setModuleName(casePath);
            	caseInfo.setTestCaseDes(caseName);
            	caseInfo.setPriName("中");
            	caseInfo.setTypeName("功能");
            	String opData = "";
            	if(!"".equals(caseCondition)) {
            		opData = caseCondition +"\n" ;
            	}
            	if(!"".equals(caseStep)) {
            		opData =opData+ caseStep +"\n" ;
            	}
            	if(!"".equals(opData)) {
            		caseInfo.setTestCaseOperData(opData);
            	}else {
            		caseInfo.setTestCaseOperData("从xmind 中导入待补充");
            	}
            	
            	if(!"".equals(caseResult)) {
            		caseInfo.setExpResult(caseResult);
            	}else {
            		caseInfo.setExpResult("从xmind 中导入待补充");
            	}

            	calseList.add(caseInfo);
                return;
            }
        }
    }
	
	private View buildDownLoadCaseTempl (CaseManagerDto dto ,List<ImpExpCaseInfo> caseInfoList) {
		
		
		String taskId = SecurityContextHolderHelp.getCurrTaksId();
		dto.setTaskId(taskId);
		String path = null;
		path = ServletActionContext.getServletContext().getRealPath(
				File.separator);
		
		XSSFWorkbook  wb = null;
		FileInputStream templateInputStream = null;
		try {
			path = ServletActionContext.getServletContext().getRealPath(
					File.separator);
			templateInputStream = new FileInputStream(path
					+ File.separator + "mypmUserFiles" + File.separator
					+ "template" + File.separator + "caseExpImpTmpl.xlsx");
			wb =new XSSFWorkbook (templateInputStream);
		} catch (FileNotFoundException e1) {
			logger.error(e1);
		} catch (IOException e1) {
			logger.error(e1);
		}finally {
			if(templateInputStream!=null) {
				try {
					templateInputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		wb.setSheetName(0, "sheet1");
		XSSFSheet  sheet = wb.getSheetAt(0);
		if(!"caseLibExpCase".equals(dto.getCommand())&&dto.getPckId()!=null&&!"".equals(dto.getPckId())) {//如是测试包
			TestCasePackage testPkg = impExpManagerService.findByProperties(TestCasePackage.class,new String[]{"packageId"}, new Object[] {dto.getPckId()}).get(0);
			if(testPkg!=null) {//可能后台被人删了
				dto.setTestcasepkgName(testPkg.getPackageName());
			}
			
		}
			

		XSSFRow row = sheet.getRow(3);
		if(!"caseLibExpCase".equals(dto.getCommand())) {
			SingleTestTask task = testTaskService.getProNameAndPmName(taskId);
			if (task != null) {
				if(task!=null) {
					XSSFCell cell = row.getCell(1);
					if(dto.getTestcasepkgName()!=null) {
						cell.setCellValue(task.getProName() +"  ，测试包："+dto.getTestcasepkgName() +" 导入导出通用模板");
					}else {
						cell.setCellValue(task.getProName()+" 导入导出通用模板");
					}
					XSSFCell cellPm = row.getCell(9);
					if(cellPm==null) {
						cellPm=row.createCell(9);
					}
					cellPm.setCellValue(task.getPsmName());
				}

			}
		}else {
			XSSFCell cell = row.getCell(1);
			cell.setCellValue(" 导入导出通用模板");
		}
		if(!"caseLibExpCase".equals(dto.getCommand())) {
			String softVersionHql =  "from SoftwareVersion where taskid=? and verStatus!=2";
			List<SoftwareVersion>  sfvList = testCasePackageService.findByHql(softVersionHql, taskId);
			// 加载下拉列表内容
			String[] sfvTextList = new String[sfvList.size()];
			int l = 0;
			for(SoftwareVersion sfv:sfvList) {
				sfvTextList[l]=sfv.getVersionNum();
				l++;
			}
			DataValidationHelper helper = sheet.getDataValidationHelper();
			DataValidationConstraint sfvConstraint = null;
			if(sfvTextList!=null&&sfvTextList.length>1) {
				sfvConstraint = helper.createExplicitListConstraint(sfvTextList);
			}
			
			CellRangeAddressList sfvRegions = null;
			CellRangeAddressList execResultRegions = null;
			if(caseInfoList==null||caseInfoList.isEmpty()) {
				// 设置数据有效性加载在哪个单元格上。
				// 四个参数分别是：起始行、终止行、起始列、终止列
				sfvRegions = new CellRangeAddressList( 5, 5, 7,  7);
				execResultRegions = new CellRangeAddressList( 5,  5,  6,  6);
			}else {
				for (ImpExpCaseInfo caseInfo : caseInfoList) {
					//是转为EXCEL 因为从 XMIND 中导入，和转EXCEL解析是用相机的方，所以要这里转EXCEL时，要把没值值的必填项设置为空
					if("从xmind 中导入待补充".equals(caseInfo.getTestCaseOperData())) {
						caseInfo.setTestCaseOperData("");
					}
					if("从xmind 中导入待补充".equals(caseInfo.getExpResult())) {
						caseInfo.setExpResult("");
					}
				}
				Integer  startRow =new Integer(5);
				writeExpImpExcel(caseInfoList, sheet,startRow,true);
				sfvRegions = new CellRangeAddressList( 5, 4+caseInfoList.size(), 7,  7);
				execResultRegions = new CellRangeAddressList( 5,  4+caseInfoList.size(),  6,  6);
			}
			 
			// 数据有效性对象
			//System.out.println("===========================");
//			// 加载下拉列表内容
			String[] execResult = new String[] {"未执行","通过","未通过","阻塞","不适用"};
			DataValidationConstraint execResultConstraint = helper.createExplicitListConstraint(execResult);

			
			// 数据有效性对象
			DataValidation execResult_validation = helper.createValidation(execResultConstraint, execResultRegions);
			DataValidation data_validation = null;
			if(sfvConstraint!=null) {
				data_validation = helper.createValidation(sfvConstraint, sfvRegions);
		    	data_validation.setSuppressDropDownArrow(true);  
		    	data_validation.setShowErrorBox(true); 
			}
	    	execResult_validation.setSuppressDropDownArrow(true);  
	    	execResult_validation.setShowErrorBox(true); 
	    	if(data_validation!=null) {
	    		sheet.addValidationData(data_validation);
	    	}
			
			sheet.addValidationData(execResult_validation);
		}else {
			DataValidationHelper helper = sheet.getDataValidationHelper();
			String[] execResult = new String[] {"未执行","通过","未通过","阻塞","不适用"};
			DataValidationConstraint execResultConstraint = helper.createExplicitListConstraint(execResult);
			// 数据有效性对象
			CellRangeAddressList execResultRegions = new CellRangeAddressList( 5,  5,  6,  6);
			DataValidation execResult_validation = helper.createValidation(execResultConstraint, execResultRegions);
			execResult_validation.setSuppressDropDownArrow(true);  
	    	execResult_validation.setShowErrorBox(true); 
		}

		
		FileOutputStream exportExcelFile = null;
		// 将Excel工作簿存盘
		String time = String.valueOf((new Date()).getTime());
		int userInt = SecurityContextHolderHelp.getUserId().hashCode();
		try {
			// 输出文件
			
			exportExcelFile = new FileOutputStream(path + File.separator
					+ "mypmUserFiles" + File.separator + "expFile"
					+ File.separator + "caseExportTempl_" + time + userInt+".xlsx");
			wb.write(exportExcelFile);
		} catch (IOException e) {
			logger.error(e);
		} finally {
			if (exportExcelFile != null) {
				try {
					exportExcelFile.flush();
					exportExcelFile.close();
				} catch (IOException e) {
					logger.error(e);
				}
			}
		}
		if(caseInfoList==null||caseInfoList.isEmpty()) {
			InputStream exportStuStream = null;
			exportStuStream = ServletActionContext
					.getServletContext().getResourceAsStream(
							File.separator + "mypmUserFiles" + File.separator
									+ "expFile" + File.separator + "caseExportTempl_"
									+ time + userInt+".xlsx");
			UniversalView view = new UniversalView();
			view.displayData("excel", exportStuStream);
			File file=new File(path + File.separator
					+ "mypmUserFiles" + File.separator + "expFile"
					+ File.separator + "caseExportTempl_" + time +userInt+ ".xlsx"
							+ "");
			try {
				if(exportStuStream!=null) {
					exportStuStream.close();
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			//导出的输入流己写，删除磁盘上的导出文件
			if(file.exists()&&file.isFile()) {
				file.delete();
			}
			return view;
		}else {//xmind 转excel 
			super.writeResult("success_"+(time + userInt));
			return super.globalAjax();
		}

	}

	public View downLoadXmind2ExcelFile(BusiRequestEvent req) {
		CaseManagerDto dto = super.getDto(CaseManagerDto.class, req);
		String impFilePath  =dto.getImpFilePath();
		InputStream exportStuStream = null;
		String path =  ServletActionContext.getServletContext().getRealPath(
				File.separator);
		exportStuStream = ServletActionContext
				.getServletContext().getResourceAsStream(
						File.separator + "mypmUserFiles" + File.separator
								+ "expFile" + File.separator + "caseExportTempl_"
								+ impFilePath+".xlsx");
		UniversalView view = new UniversalView();
		view.displayData("excel", exportStuStream);
		File file=new File(path + File.separator
				+ "mypmUserFiles" + File.separator + "expFile"
				+ File.separator + "caseExportTempl_" + impFilePath+ ".xlsx"
						+ "");
		try {
			if(exportStuStream!=null) {
				exportStuStream.close();
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//导出的输入流己写，删除磁盘上的导出文件
		if(file.exists()&&file.isFile()) {
			file.delete();
		}
		//view.displayData("forward", "downLoadCaseTempl");
		return view;
	}
	public View downLoadCaseTempl(BusiRequestEvent req) {
		
		CaseManagerDto dto = super.getDto(CaseManagerDto.class, req);
		return buildDownLoadCaseTempl(dto,null);
		
	}
	
	public View downLoadCaseLibCaseTempl(BusiRequestEvent req) {
		
		CaseManagerDto dto = super.getDto(CaseManagerDto.class, req);
		dto.setCommand("caseLibExpCase");
		return buildDownLoadCaseTempl(dto,null);
		
	}
	
	
	@SuppressWarnings("unchecked")
	public View expCase4Imp(BusiRequestEvent req) {
		final CaseManagerDto dto = super.getDto(CaseManagerDto.class, req);
		String taskId = SecurityContextHolderHelp.getCurrTaksId();
		if(taskId==null) {
			String pkgId = dto.getPckId();//从迭代过来的请求
			if(pkgId!=null) {
				TestCasePackage testCasePackage = impExpManagerService.getHibernateGenericController().findUniqueBy(TestCasePackage.class, "packageId", pkgId);
				taskId = testCasePackage.getTaskId();
				SecurityContextHolderHelp.setCurrTaksId(taskId);
			}
		}
		dto.setTaskId(taskId);
		impExpManagerService.buildCaseWhereSql(dto);
		int pageSize = 2000;
		dto.setPageSize(pageSize);
		int totalRows = impExpManagerService
				.getHibernateGenericController()
				.getResultCountBySqlWithValuesMap(dto.getHql(),
						dto.getHqlParamMaps()).intValue();
		if (totalRows == 0) {
			return this.downLoadCaseTempl(req);
		}
		int pageCount = (totalRows + (pageSize - (totalRows % pageSize == 0 ? pageSize
				: totalRows % pageSize)))
				/ pageSize;
		XSSFWorkbook  wb = null;
		String path = null;
		FileInputStream templateInputStream = null;
		try {
			path = ServletActionContext.getServletContext().getRealPath(
					File.separator);
			templateInputStream = new FileInputStream(path
					+ File.separator + "mypmUserFiles" + File.separator
					+ "template" + File.separator + "caseExpImpTmpl.xlsx");
			wb =new XSSFWorkbook (templateInputStream);
		} catch (FileNotFoundException e1) {
			logger.error(e1);
		} catch (IOException e1) {
			logger.error(e1);
			
		}finally {
			if(templateInputStream!=null) {
				try {
					templateInputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		wb.setSheetName(0, "sheet1");
		XSSFSheet  sheet = wb.getSheetAt(0);
		XSSFRow  rowSumary = sheet.getRow(2);
		XSSFCell  cellSumary = rowSumary.getCell((short) 0);
		Long  defaultExeVerId = null;
		String packageName = "";
		if(dto.getPckId()!=null&&!"".equals(dto.getPckId())) {//如是测试包
			TestCasePackage testPkg = impExpManagerService.findByProperties(TestCasePackage.class,new String[]{"packageId"}, new Object[] {dto.getPckId()}).get(0);
			packageName = testPkg.getPackageName();
			cellSumary.setCellValue(" 导出用例数:" + totalRows   +";其中未测试用例数为:" +testPkg.getNotExeCount());
			if(testPkg.getExecVersion()!=null&&!"".equals(testPkg.getExecVersion())) {
				defaultExeVerId = Long.parseLong(testPkg.getExecVersion());
			}
		}else{
			cellSumary.setCellValue((impExpManagerService.getCaseCountStr(taskId)
					+ ";导出用例数:" + totalRows));
		}
			
		String softVersionHql =  "from SoftwareVersion where taskid=? and verStatus!=2";
		List<SoftwareVersion>  sfvList = testCasePackageService.findByHql(softVersionHql, taskId);
		// 加载下拉列表内容
		String[] sfvTextList = new String[sfvList.size()];
		int l = 0;
		String defaultExeVersionNum= null;
		for(SoftwareVersion sfv:sfvList) {
			sfvTextList[l]=sfv.getVersionNum();
			if(defaultExeVerId!=null&&sfv.getVersionId().toString().equals(defaultExeVerId.toString())) {
				defaultExeVersionNum = sfv.getVersionNum();
			}	
			l++;
		}
		XSSFRow row = sheet.getRow(3);

		SingleTestTask task = testTaskService.getProNameAndPmName(taskId);
		if (task != null) {
			XSSFCell cell = row.getCell(1);
			if(dto.getPckId()!=null&&!"".equals(dto.getPckId())) {
				cell.setCellValue(task.getProName()+"项目  测试用例包: "+packageName);
			}else {
				cell.setCellValue(task.getProName());
			}
			
			XSSFCell cellPm = row.getCell(9);
			if(cellPm==null) {
				cellPm=row.createCell(9);
			}
			cellPm.setCellValue(task.getPsmName());
			XSSFCell cellResuorceChkCode = row.getCell(11);
			if(cellResuorceChkCode==null) {
				cellResuorceChkCode=row.createCell(11);
			}
			if(dto.getPckId()!=null&&!"".equals(dto.getPckId())) {
				cellResuorceChkCode.setCellValue(String.valueOf((dto.getPckId().toString()).hashCode()));
			}else {
				cellResuorceChkCode.setCellValue(String.valueOf((taskId.toString()).hashCode()));
			}
			
		}
		Integer startRow = 5;
		for (int i = 1; i < pageCount + 1; i++) {
			List<ImpExpCaseInfo> exportList = impExpManagerService.getJdbcTemplateWrapper().queryAllMatchListWithParaMap(dto.getHql(), ImpExpCaseInfo.class, dto.getHqlParamMaps(), i, pageSize, ""); 
			writeExpImpExcel(exportList, sheet, startRow,false);
			exportList.clear();
			startRow = pageSize + startRow;
		}
		DataValidationHelper helper = sheet.getDataValidationHelper();
		DataValidationConstraint sfvConstraint = null;
		if(sfvTextList!=null&sfvTextList.length>1) {
			sfvConstraint = helper.createExplicitListConstraint(sfvTextList);
		}
		
		CellRangeAddressList sfvRegions = new CellRangeAddressList( 5, (4+totalRows), 7,  7);
		// 数据有效性对象
		//System.out.println("===========================");
//		// 加载下拉列表内容
		String[] execResult = new String[] {"未执行","通过","未通过","阻塞","不适用"};
		DataValidationConstraint execResultConstraint = helper.createExplicitListConstraint(execResult);
		// 设置数据有效性加载在哪个单元格上。
		// 四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList execResultRegions = new CellRangeAddressList( 5,  (4+totalRows),  6,  6);
		// 数据有效性对象
		DataValidation execResult_validation = helper.createValidation(execResultConstraint, execResultRegions);
		DataValidation data_validation = null;
		if(sfvConstraint!=null) {
			data_validation = helper.createValidation(sfvConstraint, sfvRegions);
	    	data_validation.setSuppressDropDownArrow(true);  
	    	data_validation.setShowErrorBox(true); 
		}
    	execResult_validation.setSuppressDropDownArrow(true);  
    	execResult_validation.setShowErrorBox(true); 
    	if(data_validation!=null) {
    		sheet.addValidationData(data_validation);
    	}
		
		sheet.addValidationData(execResult_validation);
		if(defaultExeVersionNum!=null) {
			this.expCasePackageAddCheckCode(sheet, totalRows,defaultExeVersionNum);
		}else {
			this.addCheckCode(sheet, totalRows,false);
		}
		FileOutputStream exportExcelFile = null;
		// 将Excel工作簿存盘
		String time = String.valueOf((new Date()).getTime());
		int userInt = SecurityContextHolderHelp.getUserId().hashCode();
		try {
			// 输出文件
			
			exportExcelFile = new FileOutputStream(path + File.separator
					+ "mypmUserFiles" + File.separator + "expFile"
					+ File.separator + "caseExport_" + time + userInt+".xlsx");
			wb.write(exportExcelFile);
		} catch (IOException e) {
			logger.error(e);
		} finally {
			if (exportExcelFile != null) {
				try {
					exportExcelFile.flush();
					exportExcelFile.close();
				} catch (IOException e) {
					logger.error(e);
				}
			}
		}
		
		InputStream exportStuStream = getExpImpFileInputStream(time,userInt);
		UniversalView view = new UniversalView();
		view.displayData("excel", exportStuStream);
		File file=new File(path + File.separator
				+ "mypmUserFiles" + File.separator + "expFile"
				+ File.separator + "caseExport_" + time +userInt+ ".xlsx"
						+ "");
		//导出的输入流己写，删除磁盘上的导出文件
		try {
			exportStuStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		if(file.exists()&&file.isFile()) {
			file.delete();
		}
		return view;
	}
	
	private void expCasePackageAddCheckCode(Sheet sheet,int rowCout,String defaultExeVersion) {
		int len =5+ rowCout;
		Row row = null;
		Cell cell = null;
		for(int i = 5 ; i<len ; i++) {
			row = sheet.getRow(i);
			StringBuffer sb = new StringBuffer();
			for(int c =0 ; c<12; c++ ) {
				cell = row.getCell(c);
				if(cell==null) {//有时候，算记录数不对，这进要加这处理要不出报错，
					if(c==0) {
						return;
					}
					cell = row.createCell(c);
				}
				if(c==6) {
					cell.setCellValue("未执行");
				}
				if(c==7) {
					cell.setCellValue(defaultExeVersion);
				}
				if(c==0||c==2||c==3||c==4||c==10||c==11) {
					sb.append(cell.getStringCellValue()).append("/");
				}
				
			}
			cell = row.getCell(0);
			String id =cell.getStringCellValue() ;
			cell.setCellValue(id+"/"+(sb.toString().hashCode()));
		}
	}
	private void addCheckCode(Sheet sheet,int rowCout,boolean isPubCaseLib) {
		int len =5+ rowCout;
		Row row = null;
		Cell cell = null;
		int cLen;
		if(isPubCaseLib) {
			cLen=11;
		}else {
			cLen=12;
		}
		for(int i = 5 ; i<len ; i++) {
			row = sheet.getRow(i);
			StringBuffer sb = new StringBuffer();
			for(int c =0 ; c<cLen; c++ ) {
				cell = row.getCell(c);
				if(cell==null) {//有时候，算记录数不对，这进要加这处理要不出报错，
					if(c==0) {
						return;
					}
					cell = row.createCell(c);
				}
				if(c==6) {
					cell.setCellValue("未执行");
				}
				if(c==0||c==2||c==3||c==4||c==10||c==11) {
					sb.append(cell.getStringCellValue()).append("/");
				}
				
			}
			cell = row.getCell(0);
			String id =cell.getStringCellValue() ;
			cell.setCellValue(id+"/"+(sb.toString().hashCode()));
		}
	}
	private void writeExpImpExcel(List<ImpExpCaseInfo> caseList, XSSFSheet  sheet,
			Integer startRow,boolean isPubCaseLib) {
		int cLen= 11;
		if(!isPubCaseLib) {
			cLen=12;
		}
		for (ImpExpCaseInfo caseInfo : caseList) {
			XSSFRow  row = sheet.getRow(startRow);
			row = sheet.getRow(startRow);
			if (row == null) {
				row = sheet.createRow(startRow.shortValue());
			}
			for (int celIndex = 0; celIndex < cLen; celIndex++) {
				XSSFCell cell = row.getCell(celIndex);
				if (cell == null) {
					cell = row.createCell(celIndex);
				}
				this.writeExpImpCell(caseInfo, cell, celIndex);
			}
			startRow++;
		}
	}



	private void writeExpImpCell(ImpExpCaseInfo caseInfo, XSSFCell cell, int celIndex) {
		cell.setCellType(HSSFCell.ENCODING_UTF_16);
		if (celIndex == 0) {
			cell.setCellValue(caseInfo.getTestCaseId());
		} else if (celIndex == 1) {
			if(caseInfo.getSuperMname()!=null) { //导出时不为空
				if(caseInfo.getSuperModuleLevel()>1) {
					cell.setCellValue( caseInfo.getSuperMname() + "/" + caseInfo.getModuleName());
				}else {
					cell.setCellValue(caseInfo.getModuleName());
				}
				
			}else {
				cell.setCellValue(caseInfo.getModuleName());
			}
			
		} else if (celIndex == 2) {
			cell.setCellValue(caseInfo.getTestCaseDes());
		} else if (celIndex == 3) {
			cell.setCellValue(caseInfo.getTestCaseOperData());
		} else if (celIndex == 4) {
			cell.setCellValue(caseInfo.getExpResult());
		} else if (celIndex ==5) {
			cell.setCellValue(convCase(caseInfo.getStatus()));
		} else if (celIndex == 8) {
			cell.setCellValue(caseInfo.getTypeName());
		} else if (celIndex == 9) {
			cell.setCellValue(caseInfo.getPriName());
		} else if (celIndex == 10) {
			cell.setCellValue(caseInfo.getRemark()!=null?caseInfo.getRemark():"");
		}else if (celIndex == 11) {
			cell.setCellValue(caseInfo.getTags()!=null?caseInfo.getTags():"");
		} 
 

	}

	

	public static String convCase(Integer testStatus) {
		if (testStatus == 0) {
			// return "ReView" ;
			return "待审核";
		} else if (testStatus == 1) {
			return "未测试";
		} else if (testStatus == 2) {
			return "通过";
		} else if (testStatus == 3) {
			return "未通过";
		} else if (testStatus == 4) {
			return "不适用";
		} else if (testStatus == 5) {
			return "阻塞";
		} else if (testStatus == 6) {
			return "待修正";
		}
		return "";
	}
	
	public static int  convCaseStatus(String testStatus) {
		if ("待审核".equals(testStatus)) {
			// return "ReView" ;
			return 0;
		} else if ("未测试".equals(testStatus)||"未执行".equals(testStatus)) {
			return  1;
		} else if ("通过".equals(testStatus)) {
			return 2;
		} else if ("未通过".equals(testStatus)) {
			return 3 ;
		} else if ("不适用".equals(testStatus)) {
			return 4 ;
		} else if ("阻塞".equals(testStatus)) {
			return 5 ;
		} else if ("待修正".equals(testStatus)) {
			return 6;
		}
		return 1;
	}



	public View impCase(BusiRequestEvent req)  {

		CaseManagerDto dto = super.getDto(CaseManagerDto.class, req);
		String taskId = SecurityContextHolderHelp.getCurrTaksId();
		if(taskId==null) {
			String pkgId = dto.getPckId();//从迭代过来的请求
			if(pkgId!=null) {
				TestCasePackage testCasePackage = impExpManagerService.getHibernateGenericController().findUniqueBy(TestCasePackage.class, "packageId", pkgId);
				taskId = testCasePackage.getTaskId();
				SecurityContextHolderHelp.setCurrTaksId(taskId);
			}
		}
		dto.setTaskId(taskId);
		if (dto.getImportFileFileName().endsWith("xmind")) {
			return this.impFromXmind(req);
		} 
		StringBuffer sb = new StringBuffer();
		ImpCaseFromExpVo  ipmCaseVo = this.buildImpCaseInfoFromExp(dto, sb,false)  ;
		if(ipmCaseVo==null) {
			super.writeResult(sb.toString());
			return super.globalAjax();	
		}
		if(ipmCaseVo.getNewCaseList().size()==0&&ipmCaseVo.getOffLineCaseList().size()==0&&ipmCaseVo.getOffLineExeCaseList().size()==0) {
			super.writeResult("离线处理数为0，将不执行任何同步操作");
			return super.globalAjax();	
		}
		this.execImpCaeFromExp(ipmCaseVo, dto,false);
		String pkgId = dto.getPckId()==null&&!"".equals(dto.getPckId())?dto.getPckId():dto.getTestCasePackId();
		if(pkgId!=null&&!"".equals(pkgId)) {
			//计算执行率
			testCasePackageService.reWriteEexRatio(pkgId);
		}
		if(dto.getCustomMessage()!=null&&!"".equals(dto.getCustomMessage())) {
			super.writeResult(dto.getCustomMessage());
		}else {
			super.writeResult("success");
		}
		return super.globalAjax();
	}
	
	public View impCaseFromExp(BusiRequestEvent req) throws Exception {
		
		CaseManagerDto dto = super.getDto(CaseManagerDto.class, req);
		StringBuffer sb = new StringBuffer();
		ImpCaseFromExpVo  ipmCaseVo = this.buildImpCaseInfoFromExp(dto, sb,false)  ;
		if(ipmCaseVo==null) {
			super.writeResult(sb.toString());
			return super.globalAjax();	
		}
		if(ipmCaseVo.getNewCaseList().size()==0&&ipmCaseVo.getOffLineCaseList().size()==0&&ipmCaseVo.getOffLineExeCaseList().size()==0) {
			super.writeResult("离线处理数为0，将不执行任何同步操作");
			return super.globalAjax();	
		}
		this.execImpCaeFromExp(ipmCaseVo, dto,false);
		String pkgId = dto.getPckId()==null&&!"".equals(dto.getPckId())?dto.getPckId():dto.getTestCasePackId();
		if(pkgId!=null&&!"".equals(pkgId)) {
			//计算执行率
			testCasePackageService.reWriteEexRatio(pkgId);
		}
		if(dto.getCustomMessage()!=null&&!"".equals(dto.getCustomMessage())) {
			super.writeResult(dto.getCustomMessage());
		}else {
			super.writeResult("success");
		}
		
		return super.globalAjax();
	}
	public View caseLibExpCase(BusiRequestEvent req) throws Exception {
		final CaseManagerDto dto = super.getDto(CaseManagerDto.class, req);
		String companyId = SecurityContextHolderHelp.getCompanyId();
		dto.setTaskId(companyId);
		dto.setCommand("caseLibExpCase");
		impExpManagerService.buildCaseLibCaseWhereSql(dto);
		int pageSize = 2000;
		dto.setPageSize(pageSize);
		int totalRows = impExpManagerService
				.getHibernateGenericController()
				.getResultCountBySqlWithValuesMap(dto.getHql(),
						dto.getHqlParamMaps()).intValue();
		if (totalRows == 0) {
			return this.downLoadCaseTempl(req);
		}

		int pageCount = (totalRows + (pageSize - (totalRows % pageSize == 0 ? pageSize
				: totalRows % pageSize)))
				/ pageSize;
		XSSFWorkbook  wb = null;
		String path = null;
		FileInputStream templateInputStream = null;
		try {
			path = ServletActionContext.getServletContext().getRealPath(
					File.separator);
			templateInputStream = new FileInputStream(path
					+ File.separator + "mypmUserFiles" + File.separator
					+ "template" + File.separator + "caseExpImpTmpl.xlsx");
			wb =new XSSFWorkbook (templateInputStream);
		} catch (FileNotFoundException e1) {
			logger.error(e1);
		} catch (IOException e1) {
			logger.error(e1);
			
		}finally {
			if(templateInputStream!=null) {
				try {
					templateInputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		wb.setSheetName(0, "sheet1");
		XSSFSheet  sheet = wb.getSheetAt(0);
		XSSFRow  rowSumary = sheet.getRow(2);
		XSSFCell  cellSumary = rowSumary.getCell((short) 0);
			cellSumary.setCellValue((impExpManagerService.getCaseLibCaseCountStr(companyId)
					+ ";导出用例数:" + totalRows));
		XSSFRow row = sheet.getRow(3);
		XSSFCell cell = row.getCell(1);
		cell.setCellValue("从用例库导出用例");
		XSSFCell cellPm = row.getCell(9);
		if(cellPm==null) {
			cellPm=row.createCell(9);
		}
		cellPm.setCellValue("");
		XSSFCell cellResuorceChkCode = row.getCell(11);
		if(cellResuorceChkCode==null) {
			cellResuorceChkCode=row.createCell(11);
		}
		cellResuorceChkCode.setCellValue(String.valueOf((companyId.toString()).hashCode()));
		Integer startRow = 5;
		for (int i = 1; i < pageCount + 1; i++) {
			List<ImpExpCaseInfo> exportList = impExpManagerService.getJdbcTemplateWrapper().queryAllMatchListWithParaMap(dto.getHql(), ImpExpCaseInfo.class, dto.getHqlParamMaps(), i, pageSize, ""); 
			writeExpImpExcel(exportList, sheet, startRow,true);
			exportList.clear();
		}
		DataValidationHelper helper = sheet.getDataValidationHelper();
//		// 加载下拉列表内容
		String[] execResult = new String[] {"未执行","通过","未通过","阻塞","不适用"};
		DataValidationConstraint execResultConstraint = helper.createExplicitListConstraint(execResult);
		// 设置数据有效性加载在哪个单元格上。
		// 四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList execResultRegions = new CellRangeAddressList( 5,  (4+totalRows),  6,  6);
		// 数据有效性对象
		DataValidation execResult_validation = helper.createValidation(execResultConstraint, execResultRegions);
    	execResult_validation.setSuppressDropDownArrow(true);  
    	execResult_validation.setShowErrorBox(true); 
		sheet.addValidationData(execResult_validation);
		this.addCheckCode(sheet, totalRows,true);
		FileOutputStream exportExcelFile = null;
		// 将Excel工作簿存盘
		String time = String.valueOf((new Date()).getTime());
		int userInt = SecurityContextHolderHelp.getUserId().hashCode();
		try {
			// 输出文件
			
			exportExcelFile = new FileOutputStream(path + File.separator
					+ "mypmUserFiles" + File.separator + "expFile"
					+ File.separator + "caseExport_" + time + userInt+".xlsx");
			wb.write(exportExcelFile);
		} catch (IOException e) {
			logger.error(e);
		} finally {
			if (exportExcelFile != null) {
				try {
					exportExcelFile.flush();
					exportExcelFile.close();
				} catch (IOException e) {
					logger.error(e);
				}
			}
		}
		
		InputStream exportStuStream = getExpImpFileInputStream(time,userInt);
		UniversalView view = new UniversalView();
		view.displayData("excel", exportStuStream);
		File file=new File(path + File.separator
				+ "mypmUserFiles" + File.separator + "expFile"
				+ File.separator + "caseExport_" + time +userInt+ ".xlsx"
						+ "");
		//导出的输入流己写，删除磁盘上的导出文件
		try {
			exportStuStream.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(file.exists()&&file.isFile()) {
			file.delete();
		}
		return view;
	}
	public View caseLibImpCase(BusiRequestEvent req) throws Exception {
		
		CaseManagerDto dto = super.getDto(CaseManagerDto.class, req);
		StringBuffer sb = new StringBuffer();
		ImpCaseFromExpVo  ipmCaseVo = this.buildImpCaseInfoFromExp(dto, sb,true)  ;
		if(ipmCaseVo==null) {
			super.writeResult(sb.toString());
			return super.globalAjax();	
		}
		if(ipmCaseVo.getNewCaseList().size()==0&&ipmCaseVo.getOffLineCaseList().size()==0) {
			super.writeResult("离线处理数为0，将不执行任何同步操作");
			return super.globalAjax();	
		}
		this.execImpCaeFromExp(ipmCaseVo, dto,true);
		if(dto.getCustomMessage()!=null&&!"".equals(dto.getCustomMessage())) {
			super.writeResult(dto.getCustomMessage());
		}else {
			super.writeResult("success");
		}
		
		return super.globalAjax();
	}
	private void execImpCaeFromExp(ImpCaseFromExpVo impCaseFromExpvO ,CaseManagerDto dto,boolean isCaseLibImp) {
		
		String pkgId = dto.getPckId();
		String taskId = dto.getTaskId();
		StringBuilder sb = new StringBuilder();
		String currImpChkCode = null;
		if(pkgId!=null&&!isCaseLibImp) {
			currImpChkCode = String.valueOf(pkgId.trim().toString().hashCode());
		}else if(isCaseLibImp) {//用例库中导放入用这个作源校验码，导出时，用这个CompanyId 写的
			currImpChkCode =String.valueOf( SecurityContextHolderHelp.getCompanyId().toString().hashCode());
		}else {
			currImpChkCode = String.valueOf(taskId.trim().toString().hashCode());
		}
		//更改用例
		List<TestCaseInfo> offLineCaseList = impCaseFromExpvO.getOffLineCaseList();
		if(offLineCaseList!=null&&!offLineCaseList.isEmpty()) {
			if(!currImpChkCode.equals(dto.getExpChkCode())) {
				sb.append("因源校验码不匹配 "+offLineCaseList.size()+"条离线修改的用例将不被同步；\n");
				offLineCaseList.clear();
			}else {
				sb.append(offLineCaseList.size()+"条离线修改的用例被同步；\n");
			}
		}
		dto.setCustomMessage(sb.toString());
		this.impUpdateCase(offLineCaseList, taskId,isCaseLibImp);
		//增加新用例
		List<TestCaseInfo> newCaseList =impCaseFromExpvO.getNewCaseList();
		//处理生成标签
		if(!isCaseLibImp) {
			saveTags(newCaseList,offLineCaseList);
		}
		//写执行记录（包中，或项目中）
		List<TestCaseInfo> offLineExeCaseList = impCaseFromExpvO.getOffLineExeCaseList();
		if(offLineExeCaseList!=null&&!offLineExeCaseList.isEmpty()) {
			if(!currImpChkCode.equals(dto.getExpChkCode())) {
				sb.append("因源校验码不匹配 "+offLineExeCaseList.size()+"条离线执行的用例将不被同步；\n");
				offLineExeCaseList.clear();
			}else {
				sb.append(offLineExeCaseList.size()+"条离线执行的用例被同步；\n");
			}
		}
		dto.setCustomMessage(sb.toString());
		if(!isCaseLibImp) {//用例库中不处理 离线执行
			this.impExeCase(offLineExeCaseList,taskId,pkgId);
		}
		
		
	
		int[] result = this.impAddCase(newCaseList, taskId, pkgId,isCaseLibImp);
		if(!currImpChkCode.equals(dto.getExpChkCode())&&newCaseList.size()>0&&sb.length()>1) {
			if(result[1]>0) {
				sb.append("只有"+newCaseList.size()+"条新增用例被执行导入 ,并同步了新增并执行的 "+result[1] +"条用例" );
			}else{
				sb.append("只有"+newCaseList.size()+"条新增用例被执行导入 \n");
			}
		}else {
			if(result[1]>0) {
				sb.append("导入新增用例"+result[0]+"条,并同步了新增并执行的 "+result[1] +"条用例" );
			}else if(newCaseList.size()>0) {
				sb.append("导入新增用例"+result[0]+"条 \n");
			}
		}
		dto.setCustomMessage(sb.toString());
		
	}
	

	
	private List<UserTag> getAllPubCaseTags(){
		
		
		String sql =  
				"	SELECT " + 
				"		ty.ENUMNAME AS tag_name " + 
				"	FROM " + 
				"		t_typedefine ty " + 
				"	WHERE " + 
				"		ty.COMP_ID = '"+SecurityContextHolderHelp.getCompanyId()+"'" + 
				"	AND ty.INDENTIFIER = 14 and ty.STATUS_FLG!='3'";
		List<UserTag> userTags = caseService.getJdbcTemplateWrapper().queryAllMatchListWithFreePra(sql, UserTag.class);
		if(userTags != null && userTags.size() > 0){
			return userTags;
		}
		return new ArrayList<UserTag>();
	}
	private void saveTags(List<TestCaseInfo> newCaseList,List<TestCaseInfo> offLineCaseList ) {
		String sql = "delete from t_typedefine  where COMP_ID = '"+SecurityContextHolderHelp.getCompanyId()+"'  AND INDENTIFIER = 14 and STATUS_FLG='3'";
		caseService.getJdbcTemplateWrapper().getJdbcTemplate().execute(sql);//标签这前是逻辑删，这里先真删
		//List<UserTag> userTags = getUserAllTags();
		List<UserTag> pubCaseTags = getAllPubCaseTags();
		Map<String,String> tagsInExcelMap = new HashMap<String,String>();
		if(offLineCaseList!=null&&!offLineCaseList.isEmpty()) {
			for(TestCaseInfo testCaseInfo:offLineCaseList) {
				if(testCaseInfo.getTags()!=null&&!"".equals(testCaseInfo.getTags().trim())) {
					String[] currTagsArr = testCaseInfo.getTags().split(" ");
					for(String tag :currTagsArr) {
						if(!"".equals(tag.trim())) {
							tagsInExcelMap.put(tag.trim(), tag.trim());
						}
					}
				}
			}
		}
		if(newCaseList!=null&&!newCaseList.isEmpty()) {
			for(TestCaseInfo testCaseInfo:newCaseList) {
				if(testCaseInfo.getTags()!=null&&!"".equals(testCaseInfo.getTags().trim())) {
					String[] currTagsArr = testCaseInfo.getTags().split(" ");
					for(String tag :currTagsArr) {
						if(!"".equals(tag.trim())) {
							tagsInExcelMap.put(tag.trim(), tag.trim());
						}
					}
				}
			}
		}
		
		if(pubCaseTags.isEmpty()&&!tagsInExcelMap.isEmpty()) {//这时加公共字典
			Iterator<String> it = tagsInExcelMap.keySet().iterator();
			String compId = SecurityContextHolderHelp.getCompanyId();
			while(it.hasNext()) {
				CasePubTag casePubTag= new CasePubTag();
				casePubTag.setTypeName(it.next());
				casePubTag.setRemark("导入用例时自动生成");
				casePubTag.setCompId(compId);
				casePubTag.setStatus("1");
				caseService.add(casePubTag);
			}
			
		}else if (!pubCaseTags.isEmpty()&&!tagsInExcelMap.isEmpty()) {//这时加个人字典
			Iterator<String> it = tagsInExcelMap.keySet().iterator();
			String compId = SecurityContextHolderHelp.getCompanyId();
			Map<String,String>  pubCaseTagsMap = new HashMap<String,String>();
			for(UserTag userTag:pubCaseTags) {
				pubCaseTagsMap.put(userTag.getTagName(), userTag.getTagName());
			}
			while(it.hasNext()) {
				String currTagName = it.next();
				if(!pubCaseTagsMap.containsKey(currTagName)) {
					CasePubTag casePubTag= new CasePubTag();
					casePubTag.setTypeName(currTagName);
					casePubTag.setRemark("导入用例时自动生成");
					casePubTag.setCompId(compId);
					casePubTag.setStatus("1");
					caseService.add(casePubTag);
				}
			}			
		}
	}
	
	private ImpCaseInfo newCaseList2ImpCaseInfo(List<TestCaseInfo> newCaseList) {
		Integer testStatus = 1;
		ImpCaseInfo impCaseInfoVo = new ImpCaseInfo();
		Date createDate = new Date();
		for(TestCaseInfo caseInfo :newCaseList) {
			TestCaseInfo tc = new TestCaseInfo();
			tc.setTestStatus(testStatus);
			if(caseInfo.getRemark()==null) {
				tc.setRemark("导入用例");
			}else {
				tc.setRemark(caseInfo.getRemark());
			}
			if(caseInfo.getTags()==null) {
				tc.setTags("");
			}else {
				tc.setTags(caseInfo.getTags());
			}
			tc.setIsReleased(0);
			tc.setCreaterId(SecurityContextHolderHelp.getUserId());
			tc.setCreatdate(createDate);
			tc.setUpddate(createDate);
			String moduleName = caseInfo.getModuleName();
			moduleName =  this.jsonProtect(moduleName);
			impCaseInfoVo.getModeMap().put(moduleName, null);
			tc.setModuleName(moduleName);
			tc.setTestCaseDes(this.jsonProtect(caseInfo.getTestCaseDes()));
			tc.setOperDataRichText(this.jsonProtect(caseInfo.getOperDataRichText()));
			tc.setExpResult(this.jsonProtect(caseInfo.getExpResult()));
			impCaseInfoVo.getCaseTypeMap().put(caseInfo.getTypeName(), caseInfo.getTypeName());
			tc.setTypeName(caseInfo.getTypeName());
			impCaseInfoVo.getCasePrimap().put(caseInfo.getPriName(), caseInfo.getPriName());
			tc.setPriName(caseInfo.getPriName());
			if(caseInfo.getTestStatus()!=null) {
				tc.setTestStatus(caseInfo.getTestStatus());
			}
			if(caseInfo.getExecVersion()!=null) {
				tc.setExecVersion(caseInfo.getExecVersion());
			}
			impCaseInfoVo.getCaseList().add(tc);
		}
		return impCaseInfoVo;
	} 
	
	private int[]  impAddCase(List<TestCaseInfo> newCaseList,String taskId,String pkgId,boolean isCaseLibImp) {
		int[] result  = new int[] {0,0};
		int addCount = 0;
		if(newCaseList!=null&&!newCaseList.isEmpty()) {
			try {
				ImpCaseInfo impCaseInfo = this.newCaseList2ImpCaseInfo(newCaseList);
				addCount = this.importCaseExe(impCaseInfo, pkgId,isCaseLibImp);
				if(!isCaseLibImp) {
					List<TestCaseInfo> caseList = impCaseInfo.getCaseList();
					//导入新增后，再把新增并离线执行的用例，写执行记录
					List<TestCaseInfo> exeCaseList = new ArrayList<TestCaseInfo>();
					if(caseList!=null&&!caseList.isEmpty()) {
						for(TestCaseInfo testCaseInfo:caseList) {
							if(testCaseInfo.getTestStatus()>=2 &&testCaseInfo.getTestStatus()<6) {
								if(testCaseInfo.getExecVersion()!=null) {
									exeCaseList.add(testCaseInfo);
								}
							}
						}
						
					}
					if(!exeCaseList.isEmpty()) {
						result[1] = exeCaseList.size();
						this.impExeCase(exeCaseList,taskId,pkgId);
					}
				}

				
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		result[0] = addCount;
		return result;
	}
	

	private int importCaseExe(ImpCaseInfo impCaseInfo,String pkgId,boolean isCaseLibImp) {
        
		if(!isCaseLibImp) {
			int addCount = this.impCaseExexute(impCaseInfo);
			//判断是否导入到用例包中
			if(!StringUtils.isNullOrEmpty(pkgId)){
				if(impCaseInfo.getCaseList()!= null && impCaseInfo.getCaseList().size() > 0){
					List<String> caseids = new ArrayList<String>();
					for(int i=0;i<impCaseInfo.getCaseList().size();i++){
						caseids.add(impCaseInfo.getCaseList().get(i).getTestCaseId().toString());
					}
					//查询原有的数据
					List<TestCase_CasePkg> modulIdList = testCasePackageService.findByProperties(TestCase_CasePkg.class,new String[]{ "packageId"},new Object[]{pkgId});
					if(modulIdList != null && modulIdList.size() > 0){
						for(int y=0;y<modulIdList.size();y++){
							caseids.add(modulIdList.get(y).getTestCaseId());
						}
					}
					if(caseids != null && caseids.size() > 0){
						String ids = "";
						for(int t=0;t<caseids.size();t++){
							if(t != caseids.size()-1){
								ids = ids + caseids.get(t) + ",";
							}else{
								ids = ids + caseids.get(t);
							}
						}
						TestCasePackageDto testCasePackageDto = new TestCasePackageDto();
						testCasePackageDto.setSelectedTestCaseIds(ids);
						testCasePackageDto.setTestCasePackage(new TestCasePackage());
						testCasePackageDto.getTestCasePackage().setPackageId(pkgId);
						testCasePackageService.saveTestCase_CasePkg(testCasePackageDto); 
					}
				}
			}
			return addCount;
		}else {
			return caseLibImpCaseExexute(impCaseInfo);
		}

	}
	
	private void impExeCase(List<TestCaseInfo> offLineExeCaseList,String taskId,String pkgId) {
		if(offLineExeCaseList!=null&&!offLineExeCaseList.isEmpty()) {
			String testcasepkgName = null;
			if(!StringUtils.isNullOrEmpty(pkgId)){
				List<TestCasePackage> pkgList=  testCasePackageService.findByProperties(TestCasePackage.class,new String[]{ "packageId","taskId"},new Object[]{pkgId,taskId});
				if(pkgList!=null&&!pkgList.isEmpty()) {
					testcasepkgName = pkgList.get(0).getPackageName();
				}
			}
			for(TestCaseInfo testCaseInfo :offLineExeCaseList) {
				CaseManagerDto dto = new CaseManagerDto();
				if(testcasepkgName!=null) {//如果测试包名为空，说明包己被删除，这时就所是在项目中导入帐
					dto.setTestCasePackId(pkgId);
					//dto.setComputeExeCountFlag("computeExeCount");
				}else {
					dto.setTestCasePackId(null);
				}
				testCaseInfo.setModuleId(testCaseInfo.getTestCaseId());
				dto.setRemark("离线执行再导入");
				dto.setTestCaseInfo(testCaseInfo);
				dto.setExeVerId(testCaseInfo.getExecVersion());
				dto.setTestcasepkgName(testcasepkgName);
				caseService.exeCase(dto);
			}
			
			
		}
	}
	private void impUpdateCase(List<TestCaseInfo> offLineCaseList,String taskId,boolean isCaseLibImp) {
		
		if(offLineCaseList!=null&&!offLineCaseList.isEmpty()) {
			if(!isCaseLibImp) {
				StringBuffer hql  = new StringBuffer();
				hql.append("update TestCaseInfo set testCaseDes=:testCaseDes,expResult=:expResult,OperDataRichText=:operData,remark=:remark,tags=:tags where taskId=:taskId and testCaseId=:testCaseId");
			
				//,operDataRichText=:operDataRichText
				List<Map<String,Object>> paraMapList = new ArrayList<Map<String,Object>>(offLineCaseList.size());
				List<CaseExeHistory> caseHisList = new ArrayList<CaseExeHistory>();
				Date update = new Date();
				for(TestCaseInfo testCaseInfo :offLineCaseList) {
					HashMap<String,Object> paraMap = new HashMap<String,Object>(5);
					paraMap.put("taskId", taskId);
					paraMap.put("testCaseId", testCaseInfo.getTestCaseId());
					paraMap.put("testCaseDes",testCaseInfo.getTestCaseDes());
					paraMap.put("operData",testCaseInfo.getOperDataRichText());
					paraMap.put("expResult",testCaseInfo.getExpResult());
					paraMap.put("remark",testCaseInfo.getRemark()==null?"":testCaseInfo.getRemark());
					paraMap.put("tags",testCaseInfo.getTags()==null?"":testCaseInfo.getTags());
					CaseExeHistory his = new CaseExeHistory();
					his.setOperaType(2);
					his.setModuleId(testCaseInfo.getTestCaseId());
					his.setTestCaseId(testCaseInfo.getTestCaseId());
					his.setTaskId(taskId);
					his.setExeDate(update);
					his.setRemark("离线修改再导入");
					his.setTestActor(SecurityContextHolderHelp.getUserId());
					paraMapList.add(paraMap);
					caseHisList.add(his);
				}
				myPmbaseService.excuteBatchHql(hql.toString(),paraMapList);
				//写更改历史
				for(CaseExeHistory his :caseHisList) {
					myPmbaseService.add(his);
				}
			}else {
				StringBuffer hql  = new StringBuffer();
				hql.append("update CaseLibTestCaseInfo set testCaseDes=:testCaseDes,expResult=:expResult,operData=:operData,remark=:remark where companyId=:companyId and testCaseId=:testCaseId");
				List<Map<String,Object>> paraMapList = new ArrayList<Map<String,Object>>(offLineCaseList.size());
				//Date update = new Date();
				String compayId = SecurityContextHolderHelp.getCompanyId();
				for(TestCaseInfo testCaseInfo :offLineCaseList) {
					HashMap<String,Object> paraMap = new HashMap<String,Object>(5);
					paraMap.put("companyId",compayId);
					paraMap.put("testCaseId", testCaseInfo.getTestCaseId());
					paraMap.put("testCaseDes",testCaseInfo.getTestCaseDes());
					paraMap.put("operData",testCaseInfo.getOperDataRichText());
					paraMap.put("expResult",testCaseInfo.getExpResult());
					paraMap.put("remark",testCaseInfo.getRemark()==null?"":testCaseInfo.getRemark());
					paraMapList.add(paraMap);
					
				}
				myPmbaseService.excuteBatchHql(hql.toString(),paraMapList);
			}

			
		}
	}
	
	private ImpCaseFromExpVo buildImpCaseInfoFromExp(CaseManagerDto dto, StringBuffer sb,boolean isCaseLibImp) {
		String officeVersion = null;
		Workbook wb = null;
		InputStream stream = null;
		try {
			if (dto.getImportFileFileName().endsWith(".xls")) {
				officeVersion = "2003";
				stream = new FileInputStream(dto.getImportFile());
			} else if (dto.getImportFileFileName().endsWith(".xlsx")) {
				officeVersion = "2007";
				stream = new FileInputStream(dto.getImportFile());
			}

			if (officeVersion.equals("2003")) {
				wb = (Workbook) new HSSFWorkbook(stream);
			} else if (officeVersion.equals("2007")) {
				wb = (Workbook) new XSSFWorkbook(stream);
			}
		} catch (FileNotFoundException e1) {
			sb.append("读文件发生错 误");
			e1.printStackTrace();
			return null;
		} catch (IOException e1) {
			sb.append("读文件发生错 误");
			e1.printStackTrace();
			return null;
		}
		Map<String,Long> sfvListMap = new HashMap<String,Long>();
		if(!isCaseLibImp) {
			String softVersionHql =  "from SoftwareVersion where taskid=? and verStatus!=2";
			List<SoftwareVersion>  sfvList = testCasePackageService.findByHql(softVersionHql, dto.getTaskId());
		
			for(SoftwareVersion sfv:sfvList) {
				sfvListMap.put(sfv.getVersionNum(), sfv.getVersionId());
				
				
			}
		}

		ImpCaseFromExpVo impCaseFromExpInfo = new ImpCaseFromExpVo();
		Sheet sheet = null;
		sheet = wb.getSheetAt(0);
		int countRow = sheet.getLastRowNum();
		Row row = null;
		Cell cell = null;
		//连续两行为空时，才认为到头了 ,因为模板事实5000行作了格式，这里先算也有效行到哪里，取数据的时候，只取到前效数据行
		int currEmtpyCount = 0;
		int firstEmptyRowNum = 0;
		int secondEmptyRowNum = 0;
		int fasctRowNum = 0;
		row = sheet.getRow(3);
		cell = row.getCell(11);
		dto.setExpChkCode(cell.getStringCellValue()); //把原来导入时，源校验码 存起来，在后续处理用例时，要用他来判断
		row = null;
		cell = null;
		int cLen=11;
		if(!isCaseLibImp) {
			cLen=12;
		}
		for (int i = 5; i < countRow; i++) {
			row = sheet.getRow(i);
			//System.out.println("i===="+i  +"   currEmtpyCount===="+currEmtpyCount);
			if(currEmtpyCount>=10) {
				if(firstEmptyRowNum==0) {
					firstEmptyRowNum = i-1;
				}else {
					secondEmptyRowNum =  i-1;
				}
			}
			
			if(firstEmptyRowNum!=0&&secondEmptyRowNum!=0&&(secondEmptyRowNum-firstEmptyRowNum)==1) {
				fasctRowNum = firstEmptyRowNum-1;
				break;
			}else if(firstEmptyRowNum!=0&&secondEmptyRowNum!=0&&(secondEmptyRowNum-firstEmptyRowNum)>1){
				firstEmptyRowNum = secondEmptyRowNum;
				secondEmptyRowNum = 0;
			}
			
			currEmtpyCount = 0;
			for(int j=0; j<cLen; j++) {
				cell = row.getCell(j);
				String content = null;
				try {
					if(cell!=null) {
						content = cell.getStringCellValue();
					}
				} catch (IllegalStateException e) {
					logger.error("导入数据格式不对，被强制转为Stirng");
					content =String.valueOf(cell.getNumericCellValue());
				}

				if(content==null||"".equals(content.trim())) {
					currEmtpyCount++;
				}
			}
		}
		// int countCell=sheet.getRow(0).getPhysicalNumberOfCells();

		int t = 0;
		boolean skipUpdateCurrRow= false; //没有校验码，点跳过更新的标记 
		boolean skipExecCurrRow= false; //用例 ID转为long 出错时，点跳过用例执行的的标记 
		if(fasctRowNum == 0){
			fasctRowNum = countRow-1;
		}
		for (int i = 5; i <=fasctRowNum; i++) {
			row = sheet.getRow(i);
			if(t>=5000){
				return impCaseFromExpInfo;
			}
			boolean isAdd = false;
			boolean isExec  = false ;
			StringBuffer checkCodeStr = new StringBuffer();
			TestCaseInfo caseInfo = new TestCaseInfo();
			
			for (int j = 0; j < cLen; j++) {
				
				cell = row.getCell(j);
				//System.out.println("i===="+i  +"   j===="+j);
				if(j==0&&(cell.getStringCellValue()==null||"".equals(cell.getStringCellValue())||"新增".equals((cell.getStringCellValue().trim()))||"add".equals((cell.getStringCellValue().trim())))){
					isAdd = true;
				}
				//不是用例库导入时，才处理离线执行
				if(!isCaseLibImp&&j==6&&cell.getStringCellValue()!=null&&!"未执行".equals(cell.getStringCellValue().trim())&&!"".equals(cell.getStringCellValue().trim())) {
					isExec = true;
				}
				String content = null;
				try {
					if(cell!=null) {
						content = cell.getStringCellValue();
					}
				} catch (IllegalStateException e) {
					logger.error("导入数据格式不对，被强制转为Stirng");
					content =String.valueOf(cell.getNumericCellValue());
				}

				if(isAdd){
					if ((content == null || content.trim().equals(""))&&((j>=1&&j<5)||(j>=8&&j<10))) {
						sb.append("第" + (i + 1) + "行 第" + (j + 1) + "列不能为空  <br>");	
						
					}else {
						if(j==1){
							caseInfo.setModuleName(content);
						}else if(j==2){
							caseInfo.setTestCaseDes(content);
						}else if(j==3){
							caseInfo.setOperDataRichText(content);
						}else if(j==4){
							caseInfo.setExpResult(content);
						}else if(j==8){
							caseInfo.setTypeName(content);
						}else if(j==9){
							caseInfo.setPriName(content);
						}else if(j==6) {
							if(content!=null) {
								caseInfo.setTestStatus(convCaseStatus(content.trim()));
							}
						}else if(j==7&&!isCaseLibImp) {
							if(content!=null) {
								caseInfo.setExecVersion(sfvListMap.get(content.trim()));
							}
						}else if(j==10) {
							if(content!=null) {
								caseInfo.setRemark(content);
							}
						}else if(j==11) {
							if(content!=null) {
								caseInfo.setTags(content);
							}
						}
					}
				}else {
					if ((content == null || content.trim().equals(""))&&j<=6&&j!=3&&j!=10) {
						sb.append("第" + (i + 1) + "行 第" + (j + 1) + "列不能为空  <br>");
					}else {
						Long currCaseId  = null;
						if(j==0) {
							String[] valueArray = content.split("/");
							if(valueArray.length==1) {
								skipUpdateCurrRow=true; //没有校验码，点跳过更新
								try {
									currCaseId = Long.valueOf(valueArray[0]);
								} catch (Exception e) {
									// TODO Auto-generated catch block
									logger.error(e.getMessage());
								}
							}else {
								caseInfo.setInitCheckCode(valueArray[1]);
								checkCodeStr.append(valueArray[0]).append("/");
								try {
									currCaseId = Long.valueOf(valueArray[0]);
								} catch (Exception e) {
									// TODO Auto-generated catch block
									logger.error(e.getMessage());
								}
							}
							if(currCaseId!=null) {
								caseInfo.setTestCaseId(currCaseId);
							}else {
								skipExecCurrRow=true;
							}

						}else {
							if(j==2) {
								caseInfo.setTestCaseDes(content);
								checkCodeStr.append(content).append("/");
							}else if(j==3) {
								caseInfo.setOperDataRichText(content);
								checkCodeStr.append(content).append("/");
							}else if(j==4) {
								caseInfo.setExpResult(content);
								checkCodeStr.append(content).append("/");
							}else if(j==6) {
								if(content!=null) {
									caseInfo.setTestStatus(convCaseStatus(content.trim()));
								}
							}else if(j==7&&!isCaseLibImp) {
								if(content!=null) {
									caseInfo.setExecVersion(sfvListMap.get(content.trim()));
								}
								
							}else if(j==10) {
								caseInfo.setRemark(content);
								checkCodeStr.append(content).append("/");
								
							}else if(j==11) {
								caseInfo.setTags(content);
								checkCodeStr.append(content).append("/");
							}
						}
						
					}
				}
				if(isExec &&j==7 &&(content == null||"".equals(content.trim()))) {
					sb.append("第" + (i + 1) + "行 第" + (j + 1) + " 未选择执行版本  <br>");
				}			
			}
			if (sb.length() > 5) {
				if(stream!=null) {
					try {
						stream.close();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				return null;
			}
			if(isAdd) {
				//新增加入这里，也有可能是新增同时又执行，如新增同时又执行，在导入时，写处理新增，再处理执
				impCaseFromExpInfo.getNewCaseList().add(caseInfo);
			}else {
				//先检查，是否作了修改
				if(!skipUpdateCurrRow&&!String.valueOf(checkCodeStr.toString().hashCode()).equals(caseInfo.getInitCheckCode())){
					impCaseFromExpInfo.getOffLineCaseList().add(caseInfo);
				}
			}
			if(!skipExecCurrRow&&isExec&&!isAdd) {//只有不是新增且执行的再加入这里，
				impCaseFromExpInfo.getOffLineExeCaseList().add(caseInfo);
			}
			t++;	
		}
	
		if(stream!=null) {
			try {
				stream.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return impCaseFromExpInfo;
	}



	private String jsonProtect(String value) {
		value = value.replaceAll("=", "\uff1d");
		value = value.replaceAll("\\$", "\uff04");
		value = value.replaceAll("\\^", "\uff3e");
		value = value.replaceAll("'", "\uff07");
		value = value.replaceAll("\\[", "\uff3b");
		value = value.replaceAll("\\]", "\uff3d");
		value = value.replaceAll("\\{", "\uff5b");
		value = value.replaceAll("\\}", "\uff5d");
		value = value.replaceAll("<", "\uff1c");
		value = value.replaceAll(">", "\uff1e");
		value = value.replaceAll("\"", "\u201d");
		//System.out.println(value);
		return value;
	}

	private int   caseLibImpCaseExexute(ImpCaseInfo impCaseInfo) {
		
		CaseLibCategory rootNode = getCaseLibRootNode() ;
		List<CaseType> caseTypeList = this.buildCaseNewBaseData(
				impCaseInfo.getCaseTypeMap(), CaseType.class);
		List<CasePri> casePriList = this.buildCaseNewBaseData(
				impCaseInfo.getCasePrimap(), CasePri.class);

		Map<String, CasePri> casePriMap = new HashMap<String, CasePri>(
				casePriList.size());

		Map<String, CaseType> caseTypeMap = new HashMap<String, CaseType>(
				casePriList.size());
		for (CaseType ct : caseTypeList) {
			caseTypeMap.put(ct.getTypeName(), ct);
		}
		for (CasePri cp : casePriList) {
			casePriMap.put(cp.getTypeName(), cp);
		}
		//处理模块数据
		Map<String,CaseLibCategory> caseModelPathMap  = this.handCaseLibCaseLibCategorylData(impCaseInfo, rootNode);
				
		//处理用例优先及和用例类型
		Iterator<Entry<String, CasePri>>  it = casePriMap.entrySet().iterator();
		while(it.hasNext()){
			Entry<String, CasePri> me = it.next();
			if(me.getValue().getTypeId()==null){
				try {
					outLineService.add(me.getValue());
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			
		}

		Iterator<Entry<String, CaseType>>  it2 = caseTypeMap.entrySet().iterator();
		while(it2.hasNext()){
			Entry<String, CaseType> me = it2.next();
			if(me.getValue().getTypeId()==null){
				try {
					outLineService.add(me.getValue());
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			
		}
		String companyId = SecurityContextHolderHelp.getCompanyId();
		// 一定要放在处理了优先级和类型后
		for(TestCaseInfo tc :impCaseInfo.getCaseList() ){
			
			try {
				tc.setTaskId(companyId);
				tc.setCaseTypeId(caseTypeMap.get(tc.getTypeName()).getTypeId());
				tc.setModuleId(caseModelPathMap.get(tc.getModuleName()).getCategoryId());
				tc.setModuleNum(caseModelPathMap.get(tc.getModuleName()).getCategoryNum());
				tc.setPriId(casePriMap.get(tc.getPriName()).getTypeId());
				
			} catch (Exception e) {
				
				e.printStackTrace();
			}
		}
		caseModelPathMap.clear();
		//因为重用原来的项目中用例导入的代码，处理完用你后，要转用用例库中用例，再写数据库
		return testCaseLibManagerService.saveImpotTestCase(this.convertTestCaseInfo2CaseLibTestCaseInfo(impCaseInfo.getCaseList()));
	}

	private List<CaseLibTestCaseInfo> convertTestCaseInfo2CaseLibTestCaseInfo(List<TestCaseInfo> caseList){
		if(caseList==null||caseList.isEmpty()) {
			return null;
		}
		Date date = new Date();
		ArrayList<CaseLibTestCaseInfo>  libCaseList = new ArrayList<CaseLibTestCaseInfo>(caseList.size());
		String createrId = SecurityContextHolderHelp.getUserId();
		for(TestCaseInfo testCaseInfo:caseList) {
			CaseLibTestCaseInfo libCaseInfo = new CaseLibTestCaseInfo();
			libCaseInfo.setCasePri(testCaseInfo.getPriId());
			libCaseInfo.setCaseType(testCaseInfo.getCaseTypeId());
			libCaseInfo.setCategoryId(testCaseInfo.getModuleId());
			libCaseInfo.setCategoryNum(testCaseInfo.getModuleNum());
			libCaseInfo.setCompanyId(testCaseInfo.getTaskId());
			libCaseInfo.setCreatDate(date);
			libCaseInfo.setCreaterId(createrId);
			libCaseInfo.setExamineStatus("1");
			libCaseInfo.setExpResult(testCaseInfo.getExpResult());
			libCaseInfo.setOperData(testCaseInfo.getOperDataRichText());
			libCaseInfo.setRemark(testCaseInfo.getRemark());
			libCaseInfo.setTestCaseDes(testCaseInfo.getTestCaseDes());
			libCaseInfo.setUpdDate(date);
			libCaseInfo.setWeight(2);
			libCaseList.add(libCaseInfo);
		}
		return libCaseList;
	}
	public int  impCaseExexute(ImpCaseInfo impCaseInfo) {

		OutlineInfo rootNode = this.getRootNode();
		List<CaseType> caseTypeList = this.buildCaseNewBaseData(
				impCaseInfo.getCaseTypeMap(), CaseType.class);
		List<CasePri> casePriList = this.buildCaseNewBaseData(
				impCaseInfo.getCasePrimap(), CasePri.class);

		Map<String, CasePri> casePriMap = new HashMap<String, CasePri>(
				casePriList.size());

		Map<String, CaseType> caseTypeMap = new HashMap<String, CaseType>(
				casePriList.size());
		for (CaseType ct : caseTypeList) {
			caseTypeMap.put(ct.getTypeName(), ct);
		}
		for (CasePri cp : casePriList) {
			casePriMap.put(cp.getTypeName(), cp);
		}
		//处理模块数据
		Map<String,OutlineInfo> caseModelPathMap  = this.handModelData(impCaseInfo, rootNode);
				
		//处理用例优先及和用例类型
		Iterator<Entry<String, CasePri>>  it = casePriMap.entrySet().iterator();
		while(it.hasNext()){
			Entry<String, CasePri> me = it.next();
			if(me.getValue().getTypeId()==null){
				try {
					outLineService.add(me.getValue());
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			
		}

		Iterator<Entry<String, CaseType>>  it2 = caseTypeMap.entrySet().iterator();
		while(it2.hasNext()){
			Entry<String, CaseType> me = it2.next();
			if(me.getValue().getTypeId()==null){
				try {
					outLineService.add(me.getValue());
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			
		}
		// 一定要放在处理了优先级和类型后
		for(TestCaseInfo tc :impCaseInfo.getCaseList() ){
			String taskId = SecurityContextHolderHelp.getCurrTaksId();
			try {
				tc.setTaskId(taskId);
				tc.setCaseTypeId(caseTypeMap.get(tc.getTypeName()).getTypeId());
				tc.setModuleId(caseModelPathMap.get(tc.getModuleName()).getModuleId());
				tc.setModuleNum(caseModelPathMap.get(tc.getModuleName()).getModuleNum());
				tc.setPriId(casePriMap.get(tc.getPriName()).getTypeId());
				
			} catch (Exception e) {
				
				e.printStackTrace();
			}
		}
		caseModelPathMap.clear();
		return caseService.saveImpotTestCase(impCaseInfo.getCaseList());

	}

	private synchronized Map<String,CaseLibCategory>  handCaseLibCaseLibCategorylData(ImpCaseInfo impCaseInfo,CaseLibCategory rootNode ) {
		Map<String, String> intModePathMap = impCaseInfo.getModeMap();
		List<CaseLibCategory> exitsOutlineList = loadAllCaseLibCategoryBySql();
		Map<String,CaseLibCategory> exitsOutlineMapWithLevel = new HashMap<String,CaseLibCategory>(exitsOutlineList.size());
		Map<String,CaseLibCategory> caseModelPathMap  = new HashMap<String,CaseLibCategory>(exitsOutlineList.size());
		for(CaseLibCategory outLine :exitsOutlineList) {
			//exitsOutlineMap.put(outLine.getModuleId().toString(), outLine);
			exitsOutlineMapWithLevel.put(outLine.getSuperId().toString()+"_"+outLine.getCategoryName()+"_"+outLine.getCategoryLevel().toString(), outLine);
		}
		Iterator<?> it = intModePathMap.entrySet().iterator();
		String comapnyId = SecurityContextHolderHelp.getCompanyId();
		//String taskId = SecurityContextHolderHelp.getCurrTaksId();
		while (it.hasNext()) {
			@SuppressWarnings("unchecked")
			Map.Entry<String, String> me = (Map.Entry<String, String>) it
					.next();
			String caseModelPath = me.getKey();
			String[] caseNodeArr = caseModelPath.split("/");
			String modelPath = caseNodeArr[0];
			CaseLibCategory  parentTemp = rootNode;
			Date currDate = new Date();
			for(int i =0 ; i<caseNodeArr.length; i++) {
				String currModelName = caseNodeArr[i];
				if(i!=0) {
					modelPath = modelPath +"/" +caseNodeArr[i];
				}
				CaseLibCategory outTemp =null;
				//if(i==0) {
				outTemp = exitsOutlineMapWithLevel.get(parentTemp.getCategoryId().toString()+"_"+currModelName+"_"+String.valueOf((i+2)));
				if(outTemp!=null) {
					if(intModePathMap.containsKey(modelPath)) {
						caseModelPathMap.put(modelPath, outTemp);
					}
					
					parentTemp= outTemp;
				}else {
					CaseLibCategory caseLibCategoryNewAdd   = new CaseLibCategory();
					caseLibCategoryNewAdd.setCategoryName(currModelName);
					caseLibCategoryNewAdd.setIsleafNode(1);
					caseLibCategoryNewAdd.setCategoryState(0);
					caseLibCategoryNewAdd.setCompanyId(comapnyId);
					caseLibCategoryNewAdd.setCategoryLevel(i+2);
					caseLibCategoryNewAdd.setSuperId(parentTemp.getCategoryId());
					caseLibCategoryNewAdd.setCompanyId(comapnyId);
					caseLibCategoryNewAdd.setCreatDate(currDate);  
					caseLibCategoryNewAdd.setUpdDate(currDate);
					caseModelPathMap.put(modelPath, caseLibCategoryNewAdd);
					String moduleNum = getCaseLibCategoryNextNum(parentTemp.getCategoryId().toString(), parentTemp.getCategoryNum());
					String seqStr = moduleNum.substring(moduleNum.length() - 2);
					int count = Integer.parseInt(seqStr);
					caseLibCategoryNewAdd.setCategoryNum(moduleNum);
					caseLibCategoryNewAdd.setCategorySeq(count);
					outLineService.add(caseLibCategoryNewAdd);
					if(intModePathMap.containsKey(modelPath)) {
						caseModelPathMap.put(modelPath, caseLibCategoryNewAdd);
					}
					
					if(!"0".equals(parentTemp.getSuperId().toString())&&(parentTemp.getIsleafNode()==null||parentTemp.getIsleafNode()==1)){
						parentTemp.setIsleafNode(0);
						if(parentTemp.getCategoryId()!=null){
							String hql = "update CaseLibCategory set isleafNode=0 where categoryId="+parentTemp.getCategoryId(); 
							//outLineService.update(parent);
							outLineService.executeUpdateByHql(hql, null);
						}
					}	
					exitsOutlineMapWithLevel.put(parentTemp.getCategoryId().toString()+"_"+caseLibCategoryNewAdd.getCategoryName()+"_"+caseLibCategoryNewAdd.getCategoryLevel().toString(), caseLibCategoryNewAdd);
					parentTemp= caseLibCategoryNewAdd;
				}
			}
			
		}
		exitsOutlineList.clear();
		exitsOutlineMapWithLevel.clear();
		return caseModelPathMap;
	}
	
	public String getCaseLibCategoryNextNum(String parentId, String parentNum) {

		String maxLevelSql = null;
		maxLevelSql = "select IfNull(max(t.category_num),0) as count  from t_case_lib_category t  where t.super_id = ?";
		List<Object> numList = outLineService.findBySql(maxLevelSql, null,
				parentId);
		String moduleNum = numList.get(0).toString();
		if ("0".equals(moduleNum)) {
			if (parentNum == null) {
				return "001";
			}
			return parentNum + "001";
		}
		String countStr = moduleNum.substring(moduleNum.length() - 3);
		int count = Integer.parseInt(countStr);
		count++;
		String prefix = moduleNum.substring(0, moduleNum.lastIndexOf(countStr));
		String nextNum = prefix + String.valueOf((count + 1000)).substring(1);
		return nextNum;
	}
	private synchronized Map<String,OutlineInfo>  handModelData(ImpCaseInfo impCaseInfo,OutlineInfo rootNode ) {
		Map<String, String> intModePathMap = impCaseInfo.getModeMap();
		List<OutlineInfo> exitsOutlineList = loadAllOutLineBySql();
		Map<String,OutlineInfo> exitsOutlineMapWithLevel = new HashMap<String,OutlineInfo>(exitsOutlineList.size());
		Map<String,OutlineInfo> caseModelPathMap  = new HashMap<String,OutlineInfo>(exitsOutlineList.size());
		for(OutlineInfo outLine :exitsOutlineList) {
			exitsOutlineMapWithLevel.put(outLine.getSuperModuleId().toString()+"_"+outLine.getModuleName()+"_"+outLine.getModuleLevel().toString(), outLine);
		}
		Iterator<?> it = intModePathMap.entrySet().iterator();
		String comapnyId = SecurityContextHolderHelp.getCompanyId();
		String taskId = SecurityContextHolderHelp.getCurrTaksId();
		while (it.hasNext()) {
			@SuppressWarnings("unchecked")
			Map.Entry<String, String> me = (Map.Entry<String, String>) it
					.next();
			String caseModelPath = me.getKey();
			String[] caseNodeArr = caseModelPath.split("/");
			String modelPath = caseNodeArr[0];
			OutlineInfo  parentTemp = rootNode;
			for(int i =0 ; i<caseNodeArr.length; i++) {
				String currModelName = caseNodeArr[i];
				if(i!=0) {
					modelPath = modelPath +"/" +caseNodeArr[i];
				}
				OutlineInfo outTemp =null;
				//if(i==0) {
				outTemp = exitsOutlineMapWithLevel.get(parentTemp.getModuleId().toString()+"_"+currModelName+"_"+String.valueOf((i+2)));
				if(outTemp!=null) {
					if(intModePathMap.containsKey(modelPath)) {
						caseModelPathMap.put(modelPath, outTemp);
					}
					
					parentTemp= outTemp;
				}else {
					OutlineInfo outLineNewAdd   = new OutlineInfo();
					outLineNewAdd.setModuleName(currModelName);
					outLineNewAdd.setIsleafNode(1);
					outLineNewAdd.setModuleState(0);
					outLineNewAdd.setTaskId(taskId);
					outLineNewAdd.setModuleLevel(i+2);
					outLineNewAdd.setSuperModuleId(parentTemp.getModuleId());
					outLineNewAdd.setCompanyId(comapnyId);
					outLineNewAdd.setReqType(0);
					caseModelPathMap.put(modelPath, outLineNewAdd);
					String moduleNum = outLineBlh.getNextNum(parentTemp.getModuleId().toString(), parentTemp.getModuleNum());
					String seqStr = moduleNum.substring(moduleNum.length() - 2);
					int count = Integer.parseInt(seqStr);
					outLineNewAdd.setModuleNum(moduleNum);
					outLineNewAdd.setSeq(count);
					outLineService.add(outLineNewAdd);
					if(intModePathMap.containsKey(modelPath)) {
						caseModelPathMap.put(modelPath, outLineNewAdd);
					}
					
					if(!"0".equals(parentTemp.getSuperModuleId().toString())&&(parentTemp.getIsleafNode()==null||parentTemp.getIsleafNode()==1)){
						parentTemp.setIsleafNode(0);
						if(parentTemp.getModuleId()!=null){
							String hql = "update OutlineInfo set isleafNode=0 where moduleId="+parentTemp.getModuleId(); 
							outLineService.executeUpdateByHql(hql, null);
						}
					}	
					exitsOutlineMapWithLevel.put(parentTemp.getModuleId().toString()+"_"+outLineNewAdd.getModuleName()+"_"+outLineNewAdd.getModuleLevel().toString(), outLineNewAdd);
					parentTemp= outLineNewAdd;//这一行一定要放在后面
				}
			}
			
		}
		exitsOutlineList.clear();
		exitsOutlineMapWithLevel.clear();
		return caseModelPathMap;
	}
	
	private List<OutlineInfo> loadAllOutLineBySql() {
		
		String sql = "SELECT " + 
				"	supm.MODULENAME AS super_module_name, " + 
				"	baseOt.*, supm.MODULENUM AS super_model_num " + 
				"FROM " + 
				"	(" + 
				"		SELECT " + 
				"			t.MODULEID AS module_id," + 
				"			t.SUPERMODULEID AS super_module_id," + 
				"			t.ISLEAFNODE AS isleaf_node," + 
				"			t.MODULENAME AS module_name," + 
				"			t.MODULESTATE AS module_state," + 
				"			t.MODULENUM AS module_num," + 
				"			t.MODULELEVEL AS module_level,t.SEQ as seq  " + 
				"		FROM " + 
				"			t_outlineinfo t " + 
				"		WHERE " + 
				"			t.TASKID = ? and t.SUPERMODULEID >0 " + 
				"	) baseOt " + 
				"JOIN t_outlineinfo supm ON supm.MODULEID = baseOt.super_module_id " + 
				"WHERE " + 
				"	supm.TASKID = ?    order by baseOt.module_level,baseOt.seq ";
		String taskId = SecurityContextHolderHelp.getCurrTaksId();
		List<OutlineInfo> outLineList = outLineService.getJdbcTemplateWrapper().queryAllMatchListWithFreePra(sql,OutlineInfo.class,taskId,taskId);
		return outLineList;
		
	}

	
	private List<CaseLibCategory> loadAllCaseLibCategoryBySql() {
		
		String sql = "SELECT " + 
				"	supm.category_name AS super_category_name, " + 
				"	baseOt.*, supm.category_num AS super_category_num " + 
				"FROM " + 
				"	(" + 
				"		SELECT " + 
				"			t.category_id ," + 
				"			t.super_id ," + 
				"			t.isleaf_node ," + 
				"			t.category_name," + 
				"			t.category_state," + 
				"			t.category_num," + 
				"			t.category_level,t.category_seq  " + 
				"		FROM " + 
				"			t_case_lib_category t " + 
				"		WHERE " + 
				"			t.company_id = ? and t.super_id >0 " + 
				"	) baseOt " + 
				"JOIN t_case_lib_category supm ON supm.category_id = baseOt.super_id " + 
				"WHERE " + 
				"	supm.company_id = ?    order by baseOt.category_level,baseOt.category_seq ";
		String companyId = SecurityContextHolderHelp.getCompanyId();
		List<CaseLibCategory> outLineList = outLineService.getJdbcTemplateWrapper().queryAllMatchListWithFreePra(sql,CaseLibCategory.class,companyId,companyId);
		return outLineList;
		
	}
	
	private CaseLibCategory getCaseLibRootNode() {

		String hql = " from CaseLibCategory where companyId=? and superId =0 ";
		String companyId = SecurityContextHolderHelp.getCompanyId();
		return (CaseLibCategory) outLineService.findByNoCache(hql, companyId).get(0);

	}

	private OutlineInfo getRootNode() {

		String hql = "select new OutlineInfo(moduleId,superModuleId,isleafNode, moduleName,moduleState,moduleNum) from OutlineInfo where taskId=? and superModuleId =0 ";
		String taskId = SecurityContextHolderHelp.getCurrTaksId();
		return (OutlineInfo) outLineService.findByNoCache(hql, taskId).get(0);

	}


	private <T> List<T> buildCaseNewBaseData(Map<String, String> map,
			Class<T> clasz) {
		String hql = "select new " + clasz.getSimpleName()
				+ "(typeId, typeName) from " + clasz.getSimpleName()
				+ " where (compId=? or compId=1) ";
		String companyId = SecurityContextHolderHelp.getCompanyId();
		List<T> currList = testBaseSetService.findByHql(hql, companyId);
		List<T> newList = new ArrayList<T>();
		Iterator it = map.entrySet().iterator();
		Map.Entry<String, String> me = null;
		while (it.hasNext()) {
			boolean haveAdd = false;
			me = (Map.Entry<String, String>) it.next();
			for (TypeDefine caseType : (List<TypeDefine>) currList) {
				if (me.getValue().equals(caseType.getTypeName())) {
					haveAdd = true;
					break;
				}
			}
			if (!haveAdd) {
				if (clasz.getSimpleName().equals("CaseType")) {
					CaseType ct = new CaseType();
					ct.setCompId(companyId);
					ct.setRemark("导入用例自动生成 ");
					ct.setIsDefault(0);
					ct.setStatus("1");
					ct.setTypeName(me.getValue());
					newList.add((T) ct);
				} else {
					CasePri ct = new CasePri();
					ct.setCompId(companyId);
					ct.setRemark("导入用例自动生成");
					ct.setIsDefault(0);
					ct.setStatus("1");
					ct.setTypeName(me.getValue());
					newList.add((T) ct);
				}

			}
		}
		newList.addAll(currList);
		return newList;
	}


	private InputStream getExpImpFileInputStream(String time,int userInt) {

		InputStream studentInfoStream = ServletActionContext
				.getServletContext().getResourceAsStream(
						File.separator + "mypmUserFiles" + File.separator
								+ "expFile" + File.separator + "caseExport_"
								+ time + userInt+".xlsx");
		return studentInfoStream;
	}

	public TestTaskDetailService getTestTaskService() {
		return testTaskService;
	}

	public void setTestTaskService(TestTaskDetailService testTaskService) {
		this.testTaskService = testTaskService;
	}

	public ImpExpManagerService getImpExpManagerService() {
		return impExpManagerService;
	}

	public void setImpExpManagerService(
			ImpExpManagerService impExpManagerService) {
		this.impExpManagerService = impExpManagerService;
	}

	public TestBaseSetService getTestBaseSetService() {
		return testBaseSetService;
	}

	public void setTestBaseSetService(TestBaseSetService testBaseSetService) {
		this.testBaseSetService = testBaseSetService;
	}

	public OutLineManagerService getOutLineService() {
		return outLineService;
	}

	public void setOutLineService(OutLineManagerService outLineService) {
		this.outLineService = outLineService;
	}



	public CaseManagerService getCaseService() {
		return caseService;
	}

	public void setCaseService(CaseManagerService caseService) {
		this.caseService = caseService;
	}

	public OutLineManagerBlh getOutLineBlh() {
		return outLineBlh;
	}

	public void setOutLineBlh(OutLineManagerBlh outLineBlh) {
		this.outLineBlh = outLineBlh;
	}

	public BaseService getMyPmbaseService() {
		return myPmbaseService;
	}

	public void setMyPmbaseService(BaseService myPmbaseService) {
		this.myPmbaseService = myPmbaseService;
	}
	
	public TestCasePackageService getTestCasePackageService() {
		return testCasePackageService;
	}

	public void setTestCasePackageService(
			TestCasePackageService testCasePackageService) {
		this.testCasePackageService = testCasePackageService;
	}
	public TestCaseLibManagerService getTestCaseLibManagerService() {
		return testCaseLibManagerService;
	}
	public void setTestCaseLibManagerService(TestCaseLibManagerService testCaseLibManagerService) {
		this.testCaseLibManagerService = testCaseLibManagerService;
	}


}
